package demo.excel;

import demo.java.awt.ImageDemo;
import demo.java.lang.StringDemo;
import demo.vo.DnsVo;
import demo.vo.common.Pair;
import org.apache.batik.transcoder.Transcoder;
import org.apache.batik.transcoder.TranscoderException;
import org.apache.batik.transcoder.TranscoderInput;
import org.apache.batik.transcoder.TranscoderOutput;
import org.apache.batik.transcoder.image.PNGTranscoder;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.awt.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.StringReader;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;
import java.util.stream.Stream;


public class PoiDemo {


    @Test
    public void testAccidentStat() throws Exception {
        String path = "/Users/xmly/Downloads/result (17).xlsx";
        List<List<Object>> lines = readExcel(new File(path));
        List<Pair<String, Integer>> typeList = new ArrayList<>();
        for (List<Object> line : lines) {
            if (line.size() >= 4) {
                Integer id = ((Double) line.get(0)).intValue();
                String detail = line.get(3).toString();
                String typeStr = Stream.of(detail.split("\n"))
                        .filter(StringUtils::isNotBlank)
                        .filter(s -> s.startsWith("## "))
                        .map(s -> s.replace("## ", ""))
                        .findFirst().orElse("");
                List<String> types = Stream.of(typeStr.split(","))
                        .map(StringUtils::trim)
                        .collect(Collectors.toList());

                System.err.println(id + " - " + types);
                types.forEach(t -> typeList.add(new Pair<>(t, id)));
            }
        }
        Map<String, Set<Integer>> stat = typeList.stream()
                .collect(Collectors.groupingBy(Pair::getX, Collectors.mapping(Pair::getY, Collectors.toSet())));
        System.out.println(stat);

        stat.forEach((key, value) -> System.err.println(key + "," + value.size()));
    }

    private static void convertSVGToPNG(String svgContent, String outputPath) throws IOException, TranscoderException {
        // Create a PNG transcoder
        Transcoder transcoder = new PNGTranscoder();

        // Create the input and output for the transcoder
        TranscoderInput input = new TranscoderInput(new StringReader(svgContent));
        OutputStream outputStream = Files.newOutputStream(Paths.get(outputPath));
        TranscoderOutput output = new TranscoderOutput(outputStream);

        // Transcode the SVG to PNG
        transcoder.transcode(input, output);

        // Close the output stream
        outputStream.close();
    }

    private static String generateSVGFromExcelSheet(Sheet sheet) {
        StringBuilder svgBuilder = new StringBuilder();
        svgBuilder.append("<svg xmlns=\"http://www.w3.org/2000/svg\" width=\"400\" height=\"400\">");
        svgBuilder.append("<rect width=\"100%\" height=\"100%\" fill=\"#ffffff\"/>");

        int rowCount = sheet.getPhysicalNumberOfRows();
        int colCount = sheet.getRow(0).getPhysicalNumberOfCells();

        // Calculate cell width and height based on the SVG canvas size
        int cellWidth = 400 / colCount;
        int cellHeight = 400 / rowCount;

        // Iterate through rows and columns to create SVG rectangles for cells
        for (int i = 0; i < rowCount; i++) {
            for (int j = 0; j < colCount; j++) {
                Row row = sheet.getRow(i);
                Cell cell = row.getCell(j);
                String cellValue = cell.toString();

                // Calculate rectangle position
                int x = j * cellWidth;
                int y = i * cellHeight;

                // Create a rectangle for the cell
                svgBuilder.append(String.format("<rect x='%s' y='%s' width='%s' height='%s' fill='#f0f0f0'/>", x, y, cellWidth, cellHeight));
                svgBuilder.append(String.format("<text x='%s' y='%s' fill='#000000'>%s</text>", x + 10, y + 20, cellValue));
            }
        }

        svgBuilder.append("</svg>");
        return svgBuilder.toString();
    }

    @Test
    public void testExport() throws IOException, TranscoderException {
        String path = "/Users/xmly/Documents/domain2.xlsx";
        InputStream inputStream = Files.newInputStream(Paths.get(path));
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = workbook.getSheetAt(0);
        String svgContent = generateSVGFromExcelSheet(sheet);
        convertSVGToPNG(svgContent, "/Users/xmly/Documents/tmp/test2.png");
    }

    @Test
    public void testExportImage() throws Exception {
        String path = "/Users/xmly/Documents/domain2.xlsx";
        test(path);
    }

    public static void test(String path) throws Exception {
        int sheetNum = 0;//读取表格第几页
        int imageWidth = 0;//图片宽度
        int imageHeight = 0;//图片高度

        // int nullRowCount = 0;

        InputStream inputStream = Files.newInputStream(Paths.get(path));
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = workbook.getSheetAt(sheetNum);
        List<List<String>> excelList = readXlsx(path, sheetNum);
        List<CellRangeAddress> rangeAddress = sheet.getMergedRegions(); // 获取整个sheet中合并单元格组合的集合
        //根据读取数据，动态获得表边界行列
        int totalRow = excelList.size() + 1;
        int totalCol = excelList.get(0).size();

        //创建单元格数组，用于遍历单元格
        UserCell[][] cells = new UserCell[totalRow + 1][totalCol + 1];
        int[] rowPixPos = new int[totalRow + 1];// 存放行边界
        rowPixPos[0] = 0;
        int[] colPixPos = new int[totalCol + 1];// 存放列边界
        colPixPos[0] = 0;
        //开始遍历单元格
        for (int i = 0; i < totalRow; i++) {
            for (int j = 1; j < totalCol; j++) {
                cells[i][j] = new UserCell();
                cells[i][j].setCell(sheet.getRow(i).getCell(j));
                cells[i][j].setRow(i);
                cells[i][j].setCol(j);
                boolean ifShow = !(sheet.isColumnHidden(j) || sheet.getRow(i).getZeroHeight());
                cells[i][j].setShow(ifShow);
                // 计算所求区域宽度
                float widthPix = !ifShow ? 0 : (sheet.getColumnWidthInPixels(j)); // 如果该单元格是隐藏的，则置宽度为0
                if (i == 0) {
                    imageWidth += widthPix;
                }
                colPixPos[j + 1] = (int) (widthPix * 1.15 + colPixPos[j]);
            }
            // 计算所求区域高度
            boolean ifShow = (i >= 0); // 行序列在指定区域中间
            ifShow = ifShow && !sheet.getRow(i).getZeroHeight(); // 行序列不能隐藏
            float heightPoint = !ifShow ? 0 : (sheet.getRow(i).getHeightInPoints()); // 如果该单元格是隐藏的，则置高度为0
            imageHeight += heightPoint;
            rowPixPos[i + 1] = (int) (heightPoint * 96 / 72) + rowPixPos[i];
        }
        imageHeight = imageHeight * 96 / 72;
        imageWidth = imageWidth * 115 / 100;

        /* ------------------------------ */
        List<Grid> grids = new ArrayList<Grid>();
        for (int i = 0; i < totalRow; i++) {
            for (int j = 1; j < totalCol; j++) {
                Grid grid = new Grid();
                // 设置坐标和宽高
                grid.setX(colPixPos[j]);
                grid.setY(rowPixPos[i]);
                grid.setWidth(colPixPos[j + 1] - colPixPos[j]);
                grid.setHeight(rowPixPos[i + 1] - rowPixPos[i]);
                grid.setRow(cells[i][j].getRow());
                grid.setCol(cells[i][j].getCol());
                grid.setShow(cells[i][j].isShow());

                // 判断是否为合并单元格
                int[] isInMergedStatus = isInMerged(grid.getRow(), grid.getCol(), rangeAddress);
                if (isInMergedStatus[0] == 0 && isInMergedStatus[1] == 0) {
                    // 此单元格是合并单元格，并且不是第一个单元格，需要跳过本次循环，不进行绘制
                    continue;
                } else if (isInMergedStatus[0] != -1 && isInMergedStatus[1] != -1) {
                    // 此单元格是合并单元格，并且属于第一个单元格，则需要调整网格大小
                    int lastRowPos = Math.min(isInMergedStatus[0], totalRow - 1);
                    int lastColPos = Math.min(isInMergedStatus[1], totalCol - 1);
                    grid.setWidth(colPixPos[lastColPos + 1] - colPixPos[j]);
                    grid.setHeight(rowPixPos[lastRowPos + 1] - rowPixPos[i]);

                }
                // 单元格背景颜色
                Cell cell = cells[i][j].getCell();
                if (cell != null) {
                    CellStyle cs = cell.getCellStyle();
                    grid.setBgColor(cs.getFillForegroundColorColor());
                    // 设置字体
                    org.apache.poi.ss.usermodel.Font font = workbook.getFontAt(cs.getFontIndex());
                    grid.setFont(font);
                    // 设置前景色
                    grid.setFtColor(cs.getFillBackgroundColorColor());
                    // 设置文本
                    String strCell;
                    CellType cellType = cell.getCellType();
                    switch (cellType) {
                        case STRING:
                            strCell = cell.getStringCellValue();
                            break;
                        case NUMERIC:
                            strCell = String.valueOf(cell.getNumericCellValue());
                            break;
                        case BLANK:
                            strCell = "";
                            break;
                        case FORMULA:
                            try {
                                strCell = String.valueOf(cell.getNumericCellValue());
                            } catch (IllegalStateException e) {
                                strCell = String.valueOf(cell.getRichStringCellValue());
                            }
                            break;
                        default:
                            strCell = "";
                            break;
                    }
                    if (cell.getCellStyle().getDataFormatString().contains("0.00%")) {
                        try {
                            double dbCell = Double.valueOf(strCell);
                            strCell = new DecimalFormat("0.00").format(dbCell * 100) + "%";
                        } catch (NumberFormatException e) {
                        }
                    }
                    grid.setText(strCell.matches("\\w*\\.0") ? strCell.substring(0, strCell.length() - 2) : strCell);
                }
                grids.add(grid);
            }
        }

        // 导出图片
        ImageDemo.exportImage(imageWidth, imageHeight, grids, "png", "/Users/xmly/Documents/test.png");
        System.out.println("Output to PNG file Success!");
    }

    /**
     * 判断Excel中的单元格是否为合并单元格
     *
     * @param row
     * @param col
     * @param rangeAddress
     * @return 如果不是合并单元格返回{-1,-1},如果是合并单元格并且是一个单元格返回{lastRow,lastCol},
     * 如果是合并单元格并且不是第一个格子返回{0,0}
     */
    private static int[] isInMerged(int row, int col, List<CellRangeAddress> rangeAddress) {
        int[] isInMergedStatus = {-1, -1};
        for (CellRangeAddress cra : rangeAddress) {
            if (row == cra.getFirstRow() && col == cra.getFirstColumn()) {
                isInMergedStatus[0] = cra.getLastRow();
                isInMergedStatus[1] = cra.getLastColumn();
                return isInMergedStatus;
            }
            if (row >= cra.getFirstRow() && row <= cra.getLastRow()) {
                if (col >= cra.getFirstColumn() && col <= cra.getLastColumn()) {
                    isInMergedStatus[0] = 0;
                    isInMergedStatus[1] = 0;
                    return isInMergedStatus;
                }
            }
        }
        return isInMergedStatus;
    }

    public static List<List<String>> readXlsx(String path, int pageNum) throws Exception {
        InputStream in = Files.newInputStream(Paths.get(path));
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(in);
        List<List<String>> resultList = new ArrayList<List<String>>();
        // 遍历每一页，并循环处理当前页
        // for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets();
        // numSheet++) {
        XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(pageNum);// 获取当前页
        if (xssfSheet == null) {
            // continue;
        } else {
            // 处理当前页，并处理每一行
            for (int rowNum = 0; rowNum < xssfSheet.getLastRowNum(); rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);// 获取行数据

                int minColIX = xssfRow.getFirstCellNum();// 第一行数据
                int maxColIX = xssfRow.getLastCellNum();// 总行数
                List<String> rowList = new ArrayList<String>();
                // 遍历该行，处理该行数据
                for (int colIX = minColIX; colIX < maxColIX; colIX++) {
                    XSSFCell cell = xssfRow.getCell(colIX);// 获取单元格
                    if (cell == null) {
                        continue;
                    } else {
                        rowList.add(cell.toString());
                    }
                }
                resultList.add(rowList);
            }
        }
        // }
        return resultList;
    }

    /**
     * 读取xls文件
     *
     * @param path
     * @return
     * @throws Exception
     */
    public static List<List<String>> readXls(String path, int num) throws Exception {
        InputStream in = new FileInputStream(path);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(in);
        List<List<String>> resultList = new ArrayList<List<String>>();
        // 遍历每一页，并循环处理当前页
        // for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets();
        // numSheet++) {
        HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(num);// 获取当前页
        if (hssfSheet == null) {
            // continue;
        } else {
            // 处理当前页，并处理每一行
            for (int rowNum = 0; rowNum < hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);// 获取行数据

                int minColIX = hssfRow.getFirstCellNum();// 第一行数据
                int maxColIX = hssfRow.getLastCellNum();// 总行数
                List<String> rowList = new ArrayList<String>();
                // 遍历该行，处理该行数据
                for (int colIX = minColIX; colIX < maxColIX; colIX++) {
                    HSSFCell cell = hssfRow.getCell(colIX);// 获取单元格
                    if (cell == null) {
                        continue;
                    } else {
                        rowList.add(cell.toString());
                    }
                }
                resultList.add(rowList);
            }
        }
        // }
        return resultList;
    }

    /**
     * Using a File object allows for lower memory consumption, while an InputStream requires more memory as it has to
     * buffer the whole file.
     */
    static void demo2() throws EncryptedDocumentException, IOException {

        // Use a file
        Workbook wb = WorkbookFactory.create(new File("MyExcel.xls"));

        // Use an InputStream, needs more memory
        // Workbook wb = WorkbookFactory.create(new FileInputStream("MyExcel.xlsx"));

        Sheet sheet1 = wb.getSheetAt(0);
        for (Row row : sheet1) {
            for (Cell cell : row) {
                CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
                System.out.print(cellRef.formatAsString());
                System.out.print(" - ");

                switch (cell.getCellType()) {
                    case STRING:
                        System.out.println(cell.getRichStringCellValue().getString());
                        break;
                    case NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            System.out.println(cell.getDateCellValue());
                        } else {
                            System.out.println(cell.getNumericCellValue());
                        }
                        break;
                    case BOOLEAN:
                        System.out.println(cell.getBooleanCellValue());
                        break;
                    case FORMULA:
                        System.out.println(cell.getCellFormula());
                        break;
                    default:
                        System.out.println();
                }
            }
        }

    }


    /**
     * 生成Excel
     *
     * @param head
     * @param body
     * @return
     */
    public static Workbook create(String[] head, List<Map<String, Object>> body) {
        // 创建新的Excel 工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 在Excel工作簿中建一工作表，其名为缺省值
        XSSFSheet sheet = workbook.createSheet();
        // 在索引0的位置创建行（最顶端的行）
        XSSFRow row = sheet.createRow(0);
        // 设置excel头（第一行）的头名称
        for (int i = 0; i < head.length; i++) {
            // 在索引0的位置创建单元格（左上端）
            XSSFCell cell = row.createCell(i);
            // 定义单元格为字符串类型
            cell.setCellType(CellType.STRING);
            // 在单元格中输入一些内容
            cell.setCellValue(head[i]);
        }
        // ===============================================================
        // 添加数据
        for (int n = 0; n < body.size(); n++) {
            // 在索引1的位置创建行（最顶端的行）
            XSSFRow rowN = sheet.createRow(n + 1);
            Map<String, Object> dataMap = body.get(n);
            // ===============================================================
            for (int i = 0; i < head.length; i++) {
                // 在索引0的位置创建单元格（左上端）
                XSSFCell cell = rowN.createCell(i);
                // 定义单元格为字符串类型
                cell.setCellType(CellType.STRING);
                // 在单元格中输入一些内容
                cell.setCellValue(String.valueOf(dataMap.get(head[i])));
            }
            // ===============================================================
        }
        return workbook;
    }

    /**
     * 导出excel
     *
     * @param excel_name 导出的excel路径（需要带.xlsx)
     * @param headList   excel的标题备注名称
     * @param fieldList  excel的标题字段（与数据中map中键值对应）
     * @param dataList   excel数据
     * @throws Exception
     */
    public static void createExcel(String excel_name, String[] headList, String[] fieldList,
                                   List<Map<String, Object>> dataList) throws Exception {
        // 创建新的Excel 工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 在Excel工作簿中建一工作表，其名为缺省值
        XSSFSheet sheet = workbook.createSheet();
        // 在索引0的位置创建行（最顶端的行）
        XSSFRow row = sheet.createRow(0);
        // 设置excel头（第一行）的头名称
        for (int i = 0; i < headList.length; i++) {

            // 在索引0的位置创建单元格（左上端）
            XSSFCell cell = row.createCell(i);
            // 定义单元格为字符串类型
            cell.setCellType(CellType.STRING);
            // 在单元格中输入一些内容
            cell.setCellValue(headList[i]);
        }
        // ===============================================================
        // 添加数据
        for (int n = 0; n < dataList.size(); n++) {
            // 在索引1的位置创建行（最顶端的行）
            XSSFRow row_value = sheet.createRow(n + 1);
            Map<String, Object> dataMap = dataList.get(n);
            // ===============================================================
            for (int i = 0; i < fieldList.length; i++) {

                // 在索引0的位置创建单元格（左上端）
                XSSFCell cell = row_value.createCell(i);
                // 定义单元格为字符串类型
                cell.setCellType(CellType.STRING);
                // 在单元格中输入一些内容
                cell.setCellValue((dataMap.get(fieldList[i])).toString());
            }
            // ===============================================================
        }
        // 新建一输出文件流
        FileOutputStream fos = new FileOutputStream(excel_name);
        // 把相应的Excel 工作簿存盘
        workbook.write(fos);
        fos.flush();
        // 操作结束，关闭文件
        fos.close();
    }

    /**
     * 要求excel版本在2007以上
     *
     * @param file 文件信息
     * @return
     * @throws Exception
     */
    public static List<List<Object>> readExcel(File file) throws Exception {
        if (!file.exists()) {
            throw new Exception("找不到文件");
        }
        List<List<Object>> list = new LinkedList<List<Object>>();
        XSSFWorkbook xwb = new XSSFWorkbook(Files.newInputStream(file.toPath()));
        // 读取第一张表格内容
        XSSFSheet sheet = xwb.getSheetAt(0);
        XSSFRow row = null;
        XSSFCell cell = null;
        for (int i = (sheet.getFirstRowNum() + 1); i <= (sheet.getPhysicalNumberOfRows() - 1); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            List<Object> linked = new LinkedList<Object>();
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                Object value = null;
                cell = row.getCell(j);
                if (cell == null) {
                    continue;
                }
                switch (cell.getCellType()) {
                    case STRING:
                        // String类型返回String数据
                        value = cell.getStringCellValue();
                        break;
                    case NUMERIC:

                        // 数值类型返回double类型的数字
                        // System.out.println(cell.getNumericCellValue()+":格式："+cell.getCellStyle().getDataFormatString());
                        value = cell.getNumericCellValue();

                        break;
                    case BOOLEAN:
                        // 布尔类型
                        value = cell.getBooleanCellValue();
                        break;
                    case BLANK:
                        // 空单元格
                        break;
                    default:
                        value = cell.toString();
                }
                if (value != null && !value.equals("")) {
                    // 单元格不为空，则加入列表
                    linked.add(value);
                }
            }
            if (!linked.isEmpty()) {
                list.add(linked);
            }
        }
        return list;
    }

    /**
     * 要求excel版本在2007以上
     *
     * @param fileInputStream 文件信息
     * @return
     * @throws Exception
     */
    public static List<List<Object>> readExcel(FileInputStream fileInputStream) throws Exception {
        List<List<Object>> list = new LinkedList<List<Object>>();
        XSSFWorkbook xwb = new XSSFWorkbook(fileInputStream);
        // 读取第一张表格内容
        XSSFSheet sheet = xwb.getSheetAt(1);
        XSSFRow row = null;
        XSSFCell cell = null;
        for (int i = (sheet.getFirstRowNum() + 1); i <= (sheet.getPhysicalNumberOfRows() - 1); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            List<Object> linked = new LinkedList<Object>();
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                Object value = null;
                cell = row.getCell(j);
                if (cell == null) {
                    continue;
                }
                switch (cell.getCellType()) {
                    case STRING:
                        value = cell.getStringCellValue();
                        break;
                    case NUMERIC:

                        // System.out.println(cell.getNumericCellValue()+":格式："+cell.getCellStyle().getDataFormatString());
                        value = cell.getNumericCellValue();

                        break;
                    case BOOLEAN:
                        value = cell.getBooleanCellValue();
                        break;
                    case BLANK:
                        break;
                    default:
                        value = cell.toString();
                }
                if (value != null && !value.equals("")) {
                    // 单元格不为空，则加入列表
                    linked.add(value);
                }
            }
            if (!linked.isEmpty()) {
                list.add(linked);
            }
        }
        return list;
    }

    public static void testRewriting() throws IOException {
        File file = new File("d:\\cache\\workbook1.xls");
        InputStream inputStream = new FileInputStream(file);
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(inputStream);
        } catch (EncryptedDocumentException | IOException e) {
            e.printStackTrace();
        }
        Sheet sheet = wb.getSheetAt(0);
        int rowNumber = sheet.getPhysicalNumberOfRows();
        System.out.println(rowNumber);
        int firstRowNum = sheet.getFirstRowNum();
        System.out.println(firstRowNum);
        for (Row row : sheet) {
            for (Cell cell : row) {
                CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
                System.out.print(cellRef.formatAsString());
                System.out.print(" - ");

                switch (cell.getCellType()) {
                    case STRING:
                        System.out.println(cell.getRichStringCellValue().getString());
                        break;
                    case NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            System.out.println(cell.getDateCellValue());
                        } else {
                            System.out.println(cell.getNumericCellValue());
                        }
                        break;
                    case BOOLEAN:
                        System.out.println(cell.getBooleanCellValue());
                        break;
                    case FORMULA:
                        System.out.println(cell.getCellFormula());
                        break;
                    default:
                        System.out.println();
                }
            }
        }

        for (int i = 0; i < 20; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                row = sheet.createRow(i);
            }
            Cell cell = row.getCell(7);
            if (cell == null) {
                cell = row.createCell(7);
            }

            cell.setCellType(CellType.STRING);
            cell.setCellValue("a test");
        }

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fileOut.close();
    }


    /**
     * 解析EXCEL生成Java类
     *
     * @throws EncryptedDocumentException
     * @throws IOException
     */
    @Test
    public void generateJavaFile() throws EncryptedDocumentException, IOException {
        Workbook workbook = WorkbookFactory.create(new File("D:\\工作文件\\风控\\蜜罐\\蜜罐相关数据字典.xlsx"));
        Sheet sheet = workbook.getSheet("字段列表");
        for (Row row : sheet) {
            int rowNum = row.getRowNum();
            if (rowNum == 0) {
                continue;
            }
            String name = null;
            name = row.getCell(2).getStringCellValue();
            // if (name == null || name.isEmpty()) {
            // name = row.getCell(3).getStringCellValue();
            // }
            // if (name == null || name.isEmpty()) {
            // name = row.getCell(4).getStringCellValue();
            // }
            if (name == null || name.isEmpty()) {
                continue;
            }
            String type = row.getCell(6).getStringCellValue();
            String comment = row.getCell(7).getStringCellValue();
            System.out.println("//" + comment);
            System.out.println("@JSONField(name = \"" + name + "\")");
            System.out.println("private " + type + " " + StringDemo.javaNamingConvention(name) + ";");
            System.out.println();

        }
    }

    @Test
    public void testReadExcel() throws Exception {
        String path = "/Users/xmly/Documents/domain2.xlsx";
        List<List<Object>> lines = readExcel(new File(path));
        lines.stream().map(line -> {
            Pair<String, String> p = new Pair<>();
            p.setX((String) line.get(0));
            if (line.size() > 1) {
                String ips = (String) line.get(1);
                ips = String.join(";", ips.split("\n"));
                p.setY(ips);
            } else {
                p.setY("");
            }
            return p;
        }).distinct().map(p -> {
            DnsVo vo = new DnsVo();
            vo.setDomain(p.getX());
            vo.setIps(p.getY());
            int ipCnt = vo.getIpCnt();
            int healthCheck = vo.getDomainHealthCheckCode();
            int code = vo.getDomainResponseCode();
            boolean monitor = vo.isMonitor();
            return String.format("%s,%s,%s,%s,%s,%s", vo.getDomain(), vo.getIps(), ipCnt, code, healthCheck, monitor);
        }).forEach(System.out::println);
    }

    public static void reading_Rewriting() throws IOException, EncryptedDocumentException, InvalidFormatException {
        InputStream inp = new FileInputStream("workbook.xls");
        // InputStream inp = new FileInputStream("workbook.xlsx");

        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);
        Row row = sheet.getRow(2);
        Cell cell = row.getCell(3);
        if (cell == null) {
            cell = row.createCell(3);
        }

        cell.setCellType(CellType.STRING);
        cell.setCellValue("a test");

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("workbook.xls");
        wb.write(fileOut);
        fileOut.close();
    }

    static class UserCell {
        private Cell cell;
        private int row;
        private int col;
        private boolean show;
        private String text = "";
        private Color color = null;

        public Cell getCell() {
            return cell;
        }

        public void setCell(Cell cell) {
            this.cell = cell;
        }

        public int getRow() {
            return row;
        }

        public void setRow(int row) {
            this.row = row;
        }

        public int getCol() {
            return col;
        }

        public void setCol(int col) {
            this.col = col;
        }

        public boolean isShow() {
            return show;
        }

        public void setShow(boolean show) {
            this.show = show;
        }

        public String getText() {
            return text;
        }

        public void setText(String text) {
            this.text = text;
        }

        public Color getColor() {
            return color;
        }

        public void setColor(Color color) {
            this.color = color;
        }
    }

    public static class Grid {
        private boolean show;
        private int row; // 对应Excel中的row,也可以理解为cells[i][j]的i
        private int col; // 对应Excel中的col,也可以理解为cells[i][j]的j
        private int x; // x坐标
        private int y; // y坐标
        private int width;
        private int height;
        private String text;
        private Font font;
        //= new Font("微软雅黑", Font.PLAIN, 12);
        private Color bgColor = null;
        private Color ftColor = null;

        public int getRow() {
            return row;
        }

        public void setRow(int row) {
            this.row = row;
        }

        public int getCol() {
            return col;
        }

        public void setCol(int col) {
            this.col = col;
        }

        public int getX() {
            return x;
        }

        public void setX(int x) {
            this.x = x;
        }

        public int getY() {
            return y;
        }

        public void setY(int y) {
            this.y = y;
        }

        public boolean isShow() {
            return show;
        }

        public void setShow(boolean show) {
            this.show = show;
        }

        public int getWidth() {
            return width;
        }

        public void setWidth(int width) {
            this.width = width;
        }

        public int getHeight() {
            return height;
        }

        public void setHeight(int height) {
            this.height = height;
        }

        public String getText() {
            return text;
        }

        public void setText(String text) {
            this.text = text;
        }

        public Color getBgColor() {
            return bgColor;
        }

        /**
         * 将poi.ss.usermodel.Color 转换成 java.awt.Color
         * <a href="http://home.cnblogs.com/u/309701/" target="_blank">@param</a>
         * color
         */
        public void setBgColor(org.apache.poi.ss.usermodel.Color color) {
            this.bgColor = poiColor2awtColor(color);
        }

        public void setBgColor(java.awt.Color color) {
            this.bgColor = color;
        }

        public Color getFtColor() {
            return ftColor;
        }

        public void setFtColor(org.apache.poi.ss.usermodel.Color color) {
            this.ftColor = poiColor2awtColor(color);
        }

        public Font getFont() {
            return font;
        }

        public void setFont(org.apache.poi.ss.usermodel.Font font) {
            if (font != null) {
                this.font = new java.awt.Font(font.getFontName(), Font.BOLD, font.getFontHeight() / 20 + 2);
            }
        }

        private java.awt.Color poiColor2awtColor(org.apache.poi.ss.usermodel.Color color) {
            Color awtColor = null;
            if (color instanceof XSSFColor) { // .xlsx
                XSSFColor xc = (XSSFColor) color;
                String rgbHex = xc.getARGBHex();
                if (rgbHex != null) {
                    awtColor = new Color(Integer.parseInt(rgbHex.substring(2), 16));
                }
            } else if (color instanceof HSSFColor) { // .xls
                HSSFColor hc = (HSSFColor) color;
                short[] s = hc.getTriplet();
                if (s != null) {
                    awtColor = new Color(s[0], s[1], s[2]);
                }
            }
            return awtColor;
        }
    }

}
